<?php

/*
 * To change this template, choose Tools | Templates
* and open the template in the editor.
*/

class HT_Model_administrator_models_statistic extends Zend_Db_Table {//ten class fai viet hoa

	protected $_db;

	public function __construct() {
		$this->_db = Zend_Registry::get('dbMain');
		parent::init();
	}
	
	public function testFunc(){
		echo "Test statistic model done!";
	}
	
	//Post/like statistic
	public function getCountPostWithCond($condition="1=1") {
		$name = 'posts';
		$cond = $condition;
		$select = $this->_db->select()
							->from($name, array('count_total'=>'COUNT(*)'))
							->where($cond);
		$result = $this->_db->fetchAll($select);
		return $result;			
	}
	public function getCountLikeWithCond($condition="1=1") {
		$name = 'likes';
		$cond = $condition;
		$select = $this->_db->select()
							->from($name, array('count_total'=>'COUNT(*)'))
							->where($cond);
		$result = $this->_db->fetchAll($select);
		return $result;			
	}
	public function getCountCommentWithCond($condition="1=1") {
		$name = 'comments';
		$cond = $condition;
		$select = $this->_db->select()
							->from($name, array('count_total'=>'COUNT(*)'))
							->where($cond);
		$result = $this->_db->fetchAll($select);
		return $result;			
	}
	
	
	//Click advertising statistic
	public function getCountClickAdvertising($condition="1=1") {
		$name = 'advertising_click';
		$cond = $condition;
		$select = $this->_db->select()
							->from($name, array('advertising_click.id_advertising','count_total'=>'COUNT(*)'))
							->join('advertising', "advertising.id=advertising_click.id_advertising", array('advertising.link'))
							->where($cond)
							->group('id_advertising');
		$result = $this->_db->fetchAll($select);
		return $result;					
	}
	
	public function getDetailClickAdvertising($condition="1=1"){
		$name = 'advertising_click';
		$cond = $condition;
		$select = $this->_db->select()
							->from($name, array('advertising_click.id_advertising','advertising_click.user_name', 'advertising_click.ip_user', 'advertising_click.time'))
							->where($cond);
		$result = $this->_db->fetchAll($select);
		return $result;					
	}
	
	public function getLinkAdvertising($id_advertising){
		$name = 'advertising';
		$cond = "id = $id_advertising";
		$select = $this->_db->select()
							->from($name, array('link'))
							->where($cond);
		$result = $this->_db->fetchRow($select);
		return 	$result['link'];	
	}
	
	//News's statistic
	public function getListCategory($condition="1=1"){
		$name = 'category';
		$cond = $condition." AND category.active=1 AND news.status='active'";
		$select = $this->_db->select()
							->from($name, array('category.id', 'category.category_name'))
							->join('link_news_category', 'category.id=link_news_category.id_category', array('total_news'=>"COUNT(link_news_category.id_news)"))
							->join('news', 'news.id_news=link_news_category.id_news', array('sum_view'=>"SUM(news.view)"))
							->where($cond)
							->group("category.id");
		$result = $this->_db->fetchAll($select);
		return $result;
	}
	
	public function getNewsInCategory($condition="1-1"){
		$name = 'news';
		$cond = $condition." AND news.status='active'";
		$select = $this->_db->select()
							->from($name, array('news.id_news', 'news.title_vn', 'news.created'))
							->join('link_news_category', 'news.id_news=link_news_category.id_news', array('news.view'))
							->where($cond);
		$result = $this->_db->fetchAll($select);
		return $result;							
	}
	
	public function getCatName($id){
		$name = 'category';
		$cond = "id = $id";
		$select = $this->_db->select()
							->from($name, array('category_name'))
							->where($cond);
		$result = $this->_db->fetchRow($select);							
		return $result['category_name'];
	}
	
	
	//User's statistic
	/**
	 * Get numbers of new user and user login with condition
	 * @param $condition string
	 * @return array 
	 */
	public function getCountUser($condition="1=1"){
		$name = 'user';
		$cond = $condition;
		$select = $this->_db->select()
							->from($name, array('count_total'=>'COUNT(*)'))
							->where($cond);
		$result = $this->_db->fetchRow($select);
		return $result['count_total'];					
	}
	
	/**
	 * Get users follow condition
	 * @param $condition default "1=1"
	 * @return array
	 */
	public function getUsers($condition="1=1"){
		$name = 'user';
		$cond = $condition;
		$select = $this->_db->select()
							->from($name, array('wishid', 'last_login', 'reg_date', "user_name"))
							->where($cond);
		$result = $this->_db->fetchAll($select);
		return $result;
	}
	
	/**
	 * Get users follow condition in user_service table
	 * @param $condition default "1=1"
	 * @return array
	 */
	public function getUserService($condition="1=1"){
		$name = 'user_service';
		$cond = $condition;
		$select = $this->_db->select()
							->from($name, array('start_date'))
							->join('user','user.user_name=user_service.user_name',array('wishid', 'last_login', 'reg_date', "user_name"))
							->where($cond);
		$result = $this->_db->fetchAll($select);
		return $result;	
	}
	
	/**
	 * Get current service $user_name is using
	 * @param $user_name
	 * @return array(ser_id,ser_name)
	 */
	public function getCurrentServiceOfUser($user_name){
		$name = 'user_service';
		$cond = "user_service.user_name = $user_name";
		$select = $this->_db->select()
							->from($name, array('user_name', 'ser_id', 'start_date'))
							->join('ser_phase',"ser_phase.id = user_service.ser_id", array('name'))
							->limit(1);
		$result = $this->_db->fetchRow($select);
		return $result;					
	}
	
	/**
	 * Get numbers of user using service with condition
	 * @param $condition string
	 * @return array 
	 */	
	public function getCountUserService($condition="1=1") {
		$name = 'user_service';
		$cond = $condition;
		$select = $this->_db->select()
							->from($name, array('count_total'=>'COUNT(*)'))
							->where($cond);
		$result = $this->_db->fetchRow($select);
		return $result['count_total'];					
	}
	
	//Question and Answer statistic
	/**
	 * Get number question
	 * @param $condition string
	 * @return array
	 */
	public function getNumberQuestion($condition="1=1"){
		$name = 'posts';
		$cond = $condition." AND posts.is_question = 1";
		$select = $this->_db->select()
							->from($name, array('number_questions'=>'COUNT(*)'))
							->joinLeft('faq_cat', "posts.faq_cat_id = faq_cat.id", array('category_name'=>'faq_cat.name'))
							->where($cond)
							->group("faq_cat.id");
		$result = $this->_db->fetchAll($select);
		
		return $result;										
	}
	
	public function getNumberAnswerPartner($condition="1=1"){
		$name = 'comments';
		$cond = $condition." AND posts.is_question = 1";
		$select = $this->_db->select()
							->from($name, array('number_answer_partner'=>'COUNT(*)'))
							->join('partners', 'partners.user_name = comments.user_name', null)
							->join('posts', "posts.id = comments.post_id", null)
							->joinLeft('faq_cat', "posts.faq_cat_id = faq_cat.id", array('category_name'=>'faq_cat.name'))
							->where($cond)
							->group("faq_cat.id");
		$result = $this->_db->fetchAll($select);
		
		return $result;
	}
	
	public function getNumberUserPostQuestion($condition="1=1"){
		$name = 'posts';
		$cond = $condition." AND posts.is_question = 1";
		$select = $this->_db->select()
							->from($name, array('number_user_post_question'=>'COUNT(*)'))
							->joinLeft('faq_cat', "posts.faq_cat_id = faq_cat.id", array('category_name'=>'faq_cat.name', 'category_id'=>"faq_cat.id"))
							->group("faq_cat.id")
							->where($cond);
		$result = $this->_db->fetchAll($select);
		return $result;							
	}
	
	public function getUserPostQuestion($condition="1=1"){
		$name = 'posts';
		$cond = $condition." AND posts.is_question = 1";
		$select = $this->_db->select()
							->from($name, array('posts.user_name','posts.user_post'))
							->joinLeft("user", "posts.user_post = user.user_name" ,array("user.email"))
							->joinLeft('faq_cat', "posts.faq_cat_id = faq_cat.id", array('category_name'=>'faq_cat.name'))
							->where($cond);
		$result = $this->_db->fetchAll($select);
		return $result;							
				
	}
	
	public function getNumberAnswerUser($condition="1=1"){
		$name = 'comments';
		$cond = $condition." AND posts.is_question = 1 AND partners.user_name IS NULL";
		$select = $this->_db->select()
							->from($name, array('number_answer_user'=>'COUNT(*)'))
							->joinLeftUsing('partners', 'user_name', null)
							->join('posts', "posts.id = comments.post_id", null)
							->joinLeft('faq_cat', "posts.faq_cat_id = faq_cat.id", array('category_name'=>'faq_cat.name'))
							->where($cond)
							->group("faq_cat.id");
		$result = $this->_db->fetchAll($select);
		return $result;
	}			
	
	//User history statistic
	public function searchUsername($condition="1=1"){
		$name = "user";
		$cond = $condition;
		$select = $this->_db->select()
							->from($name, array('user_name'))
							->where($cond);
		$result = $this->_db->fetchAll($select);
		return $result;
	}

	public function getInfoUserByUsername($user_name){
		$name = "user";
		$cond = "user_name = '$user_name'";
		$select = $this->_db->select()
							->from($name)
							->where($cond);
		$result = $this->_db->fetchRow($select);
		return $result;		
	}
	public function getCountPostByUser($condition="1=1"){
		$name = "posts";
		$cond = $condition;
		$select = $this->_db->select()
							->from($name, array( 'total_post'=>"COUNT(*)"))
							->where($cond);
		$result = $this->_db->fetchRow($select);
		return $result['total_post'];		
	}
	
	public function getDetailPostByUser($condition="1=1"){
		$name = "posts";
		$cond = $condition;
		$select = $this->_db->select()
							->from($name, array('id', 'user_name', 'user_post', 'content', 'time', 'is_question', 'status'))
							->where($cond);
		$result = $this->_db->fetchAll($select);
		return $result;		
	}
	
	public function getCountCommentByUser($condition="1=1"){
		$name = "comments";
		$cond = $condition;
		$select = $this->_db->select()
							->from($name, array( 'total_comment'=>"COUNT(*)"))
							->where($cond);
		$result = $this->_db->fetchRow($select);
		return $result['total_comment'];		
	}
			
	public function getDetailCommentByUser($condition="1=1"){
		$name = "comments";
		$cond = $condition;
		$select = $this->_db->select()
							->from($name, array('id', 'user_name', 'post_id', 'content', 'status', 'time', 'is_question'))
							->where($cond);
		$result = $this->_db->fetchAll($select);
		return $result;		
	}			
	
}